﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Linq;
using System.Text.RegularExpressions;

namespace LDataAccess
{
    public abstract class ProxySubClass
    {
        protected IDbConnection conn;
        
        protected ProxySubClass(IDbConnection conn)
        {
            this.conn = conn;
        }

        protected SqlCeDataReader ExecuteReader(string command, IDbConnection conn, params object[] param)
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            try
            {
                SqlCeCommand cmd = new SqlCeCommand(SQLQueries.ResourceManager.GetString(command), (SqlCeConnection) conn);
                SetCommandParameters(param, ref cmd);
                return cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("{0}: {1}", command ,ex.Message));
            }
            
        }

        public void ExecuteNonQuery(string command, IDbConnection conn, params object[] param)
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();

            try
            {
                SqlCeCommand cmd = new SqlCeCommand(SQLQueries.ResourceManager.GetString(command), (SqlCeConnection)conn) { CommandType = CommandType.Text };
                SetCommandParameters(param, ref cmd);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                conn.Close();
            }

        }

        public object ExecuteScalar(string command, IDbConnection conn, params object[] param)
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();

            try
            {
                SqlCeCommand cmd = new SqlCeCommand(SQLQueries.ResourceManager.GetString(command), (SqlCeConnection)conn);
                SetCommandParameters(param, ref cmd);

                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT @@IDENTITY AS Id";
                cmd.Parameters.Clear();

                return cmd.ExecuteScalar();

            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            
        }

        /// <summary>
        /// Bulk Insert per SQL CE (Beta version)
        /// </summary>
        /// <param name="dr"></param>
        public void ExecuteBulkInsert(IDataReader dr, string tablename)
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();

            try
            {

                SqlCeCommand cmd = ((SqlCeConnection) conn).CreateCommand();
                cmd.CommandType = CommandType.TableDirect;

                cmd.CommandText = tablename;
                SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable);

                SqlCeUpdatableRecord rec = rs.CreateRecord();

                while (dr.Read())
                {
                    //TODO: Rendere dinamico per salvataggio dati Bulk
                    //TODO: leggere tipo da dr e settare record di conseguenza

                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        switch (dr[i].GetType().FullName.ToLower())
                        {
                            case "system.int64":
                                rec.SetInt64(i, (long) dr[i]);
                                break;

                            case "system.string":
                                rec.SetString(i, dr[i].ToString());
                                break;

                            case "system.datetime":
                                rec.SetDateTime(i, (DateTime)dr[i]);
                                break;
                        }
                    }
                    //rec.SetInt64(0, (long)dr["IDMeasurement"]);
                    //rec.SetInt64(1, (long)dr["IDProperty"]);
                    //rec.SetString(2, dr["IDProperty"].ToString());
                    //rec.SetDateTime(3, (DateTime)dr["Value"]);
                    //rec.SetInt64(4, (long)dr["IDRecording"]);

                    rs.Insert(rec);
                }
                rs.Close();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }


        private void SetCommandParameters(object[] param, ref SqlCeCommand cmd)
        {
            List<string> parametersCollection = ExtractParameters(cmd.CommandText);

            for (int i = 0; i < parametersCollection.Count; i++)
                cmd.Parameters.AddWithValue(parametersCollection[i], param.GetValue(i));
            
        }

        public abstract IDataReader List();
        public abstract IDataReader ListById(long ID);
        public abstract IDataReader Load(long ID);
        public abstract void Delete(long ID);
         
     

        protected List<string> ExtractParameters(string command)
        {
            Regex reg = new Regex("@(?:[\\w#_$]{1,128}|(?:(\\[)|\").{1,128}?(?(1)]|\"))");
            return reg.Matches(command).Cast<Match>().Select(match => match.Value).Where(v => v != "@IDENTITY").ToList();
            
        }
    }
}
